package com.cloud.school.bus.teacherhelper.base.database;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.cloud.school.bus.teacherhelper.entitys.Picture;
import com.cloud.school.bus.teacherhelper.entitys.UploadFile;

/**
 * 上传文件数据库
 * 
 * @author hongfeijia
 * 
 */
public class UploadFileDB extends SQLiteOpenHelper {

	/** 数据库名 */
	public static final String DB_NAME = "uploadfile.db";
	/** 表名 */
	public static final String TABLE_NAME = "uploadfile";

	/**照片路径保存，需要判断sd卡中得文件还是否存在*/
	public static final String PIC_PATH = "picPathString";
	/**照片名字*/
	public static final String PIC_FILE = "picFileString";
	/**照片大小,返回的时字节byte*/
	public static final String PIC_SIZE = "picSizeString";
	/**学生id,以逗号“，”分割*/
	public static final String STUDENT_ID = "studentId";
	/**班级uid*/
	public static final String CLASS_UID = "classuid";
	/**内容,不是必须*/
	public static final String INTRO = "intro";
	/**照片标签，不是必须*/
	public static final String PHOTO_TAG = "photoTag";
	/**上传者老师id*/
	public static final String TEACHER_ID = "teacherid";

	private UploadFileDB(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	public static UploadFileDB getUploadFileDB(Context context) {
		return new UploadFileDB(context, DB_NAME, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String execSQLString = "create table if not exists " + TABLE_NAME
				+ " (" + PIC_PATH + " varchar," + 
						 PIC_FILE + " varchar," + 
						 PIC_SIZE + " varchar," + 
						 STUDENT_ID + " varchar," + 
						 CLASS_UID + " varchar," + 
						 INTRO + " varchar," + 
						 PHOTO_TAG + " varchar," + 
						 TEACHER_ID + " varchar)";

		db.execSQL(execSQLString);
		Log.e("execSQLString::::::", execSQLString);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}

	public void putInsert(UploadFile uploadFile) {
		if (!isExists(uploadFile)) {
			StringBuilder stringBuilder = new StringBuilder();
			if(uploadFile.studentIdList.size() > 0){
				for (int i = 0; i < uploadFile.studentIdList.size(); i++) {
					stringBuilder.append(uploadFile.studentIdList.get(i)).append(",");
				}
				stringBuilder.delete(stringBuilder.length()-1, stringBuilder.length());
			}
			SQLiteDatabase sqLiteDatabase = getWritableDatabase();
			String sqlString = "insert into " + TABLE_NAME + " values ('"
					+ uploadFile.picPathString + "','" + 
					 uploadFile.picFileString + "','" + 
					 uploadFile.picSizeString + "','" + 
					 stringBuilder.toString() + "','" + 
					 uploadFile.classuid + "','" + 
					 uploadFile.intro + "','" + 
					 uploadFile.photoTag + "','" + 
					 uploadFile.teacherid
					+ "')";
			Log.e("sqlString insert ::::::", sqlString);
			sqLiteDatabase.execSQL(sqlString);
			sqLiteDatabase.close();

		} 

	}
	
	public void remove(UploadFile uploadFile) {
		SQLiteDatabase sqLiteDatabase = getWritableDatabase();

		sqLiteDatabase.execSQL("delete from " + TABLE_NAME + " where "
				+ PIC_PATH + "='" + uploadFile.picPathString + "'");

		sqLiteDatabase.close();
	}

	public boolean isExists(UploadFile uploadFile) {
		SQLiteDatabase sqLiteDatabase = getWritableDatabase();

		boolean flag = false;
		Cursor cursor = sqLiteDatabase.rawQuery("select * from " + TABLE_NAME
				+ " where " + PIC_PATH + "=?",
				new String[] { uploadFile.picPathString });
		flag = cursor.getCount() == 0 ? false : true;
		cursor.close();

		sqLiteDatabase.close();
		return flag;
	}

	public List<UploadFile> getUploadFileList() {
		SQLiteDatabase sqLiteDatabase = getWritableDatabase();

		List<UploadFile> uploadFiles = new ArrayList<UploadFile>();
		Cursor cursor = sqLiteDatabase.rawQuery("select * from " + TABLE_NAME,
				null);
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			UploadFile uploadFile = new UploadFile();
			/**照片路径保存，需要判断sd卡中得文件还是否存在*/
			uploadFile.picPathString = cursor.getString(0);
			/**照片名字*/
			uploadFile.picFileString = cursor.getString(1);
			/**照片大小,返回的时字节byte*/
			uploadFile.picSizeString = cursor.getString(2);
			String[] tempStudentIds = cursor.getString(3).split(",");
			List<String> tempStudentIdList = new ArrayList<String>();
			for (int j = 0; j < tempStudentIds.length; j++) {
				tempStudentIdList.add(tempStudentIds[j]);
			}
			/**学生id*/
			uploadFile.studentIdList  = tempStudentIdList;
			/**班级uid*/
			uploadFile.classuid = cursor.getString(4);
			/**内容,不是必须*/
			uploadFile.intro = cursor.getString(5);
			/**照片标签，不是必须*/
			uploadFile.photoTag = cursor.getString(6);
			/**上传者老师id*/
			uploadFile.teacherid = cursor.getString(7);

			uploadFiles.add(uploadFile);

			cursor.moveToNext();
		}
		cursor.close();

		sqLiteDatabase.close();

		return uploadFiles;
	}
}
